package com.boss.salary.dao.impl;


import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IGenerateSalaryDataDao;
import com.boss.salary.entity.AgencyVerify;
import com.boss.salary.entity.SalaryPaymentRequisitionBack;
import com.boss.salary.entity.SalaryPaymentTicket;
import com.boss.salary.entity.SalaryWarnMonth;
import com.boss.salary.vo.SalaryDataVo;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class GenerateSalaryDataDaoImpl extends BaseDaoImpl implements IGenerateSalaryDataDao {

    @Override
    public List<SalaryDataVo> queryGenerateSalaryDataPage(PaginationDTO paginationDTO, Map<String, Object> queryParam,
                                                          List<String> childrenId,String wheresql) {
        List<Object> sqlParams = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" av.is_pay_data_created status, av.id, ai.id agencyId, ai.agency_code, ")
                .append(" ai.agency_name, av.salary_month month, sum(s1) realSalary, sum(s2) shouldSalary,count(*) personCount ");
        sql.append(" from ");
        sql.append(TableName.SALARY_MONTH_END_SUBMIT).append(" sme ")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pe ")
                .append(" ON pe.person_id = sme.person_id and  sme.agency_id = pe.agency_id ")
                .append(" join  ").append(TableName.AGENCY_VERIFY).append(" av ")
                .append( " ON av.agency_id = pe.agency_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai ")
                .append(" ON av.agency_id = ai.id ");
//        sql.append(" where ai.is_payment_agency='1' and av.is_month_end = ? and av.is_thirteen = ?")
//                .append(" and pe.is_deleted = ? ")
//                .append(" and pe.is_stop_salary = ?");
//        sqlParams.add(SystemDictConstant.IS_FALSE);
//        sqlParams.add(SystemDictConstant.IS_FALSE);
//        sqlParams.add(SystemDictConstant.IS_FALSE);
//        sqlParams.add(SystemDictConstant.IS_FALSE);
//        if (!StringUtils.isEmpty(queryParam.get("province"))){
//            sql.append(" and pe.mof_div_code = ? ");
//            sqlParams.add(queryParam.get("province"));
//        }
//        if (!StringUtils.isEmpty(queryParam.get("salaCardBank"))&&!"-1".equals(queryParam.get("salaCardBank"))){
//            sql.append(" and pe.sala_card_bank = ? ");
//            sqlParams.add(queryParam.get("salaCardBank"));
//        }
//        sql.append(" and ").append(SysUtils.inSql("sme.agency_id", childrenId));
//        sql.append(wheresql);
//        if (!StringUtils.isEmpty(queryParam)) {
//            // 是否变动
//            if (!StringUtils.isEmpty(queryParam.get("changeAgency"))) {
//                if ("0".equals(queryParam.get("changeAgency"))) {
//                    sql.append(" and av.is_salary_change = 0 ");
//                } else {
//                    sql.append(" and av.is_salary_change != 0 ");
//                }
//            }
//            // 工资类别
//            if (!StringUtils.isEmpty(queryParam.get("salaryType"))) {
//                sql.append(" and pe.merit_pay_code = ? ");
//                sqlParams.add(queryParam.get("salaryType").toString());
//            }
//        }
        sql.append(" group by av.is_pay_data_created, av.id, ai.id, ai.agency_code, ai.agency_name, av.salary_month ");
        sql.append(" order by agency_code ");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(sqlParams.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql,params,new  BeanPropertyRowMapper<>(SalaryDataVo.class) );
//        return this.getJdbcTemplate().queryForList(sql.toString(),SalaryDataVo.class);//querySql, new BeanPropertyRowMapper(SalaryDataVo.class), params
    }

    @Override
    public int queryGenerateSalaryDataTotal(Map<String, Object> queryParam, List<String> childrenId,String wheresql) {
        List<Object> sqlParams = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) from ( ");
        sql.append(" select ");
        sql.append("  av.id ")
                .append(" from ")
                .append(TableName.PERSON_EXT_SUBMIT).append(" pe ")
                .append(" join  ").append(TableName.AGENCY_VERIFY).append(" av ")
                .append( " ON av.agency_id = pe.agency_id  ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai ")
                .append(" ON av.agency_id = ai.id ");
//        sql.append(" where ai.is_payment_agency='1' and av.is_month_end = ? and av.is_thirteen = ?")
//                .append(" and pe.is_deleted = ?")
//                .append(" and pe.is_stop_salary = ?");
//                //.append(" and av.salary_info_verified_code = ?");
//        sqlParams.add(SystemDictConstant.IS_FALSE);
//        sqlParams.add(SystemDictConstant.IS_FALSE);
//        sqlParams.add(SystemDictConstant.IS_FALSE);
//        // and pi.is_deleted = ?  sqlParams.add(SystemDictConstant.IS_FALSE);
//        sqlParams.add(SystemDictConstant.IS_FALSE);
//        if (!StringUtils.isEmpty(queryParam.get("province"))){
//            sql.append(" and pe.mof_div_code = ? ");
//            sqlParams.add(queryParam.get("province"));
//        }
//        if (!StringUtils.isEmpty(queryParam.get("salaCardBank"))&&!"-1".equals(queryParam.get("salaCardBank"))){
//            sql.append(" and pe.sala_card_bank = ? ");
//            sqlParams.add(queryParam.get("salaCardBank"));
//        }
//        //sqlParams.add("终审通过");
////        sql.append(" and ").append(SysUtils.inSql("pe.agency_id", childrenId));
//        //sql.append(" and ").append(SysUtils.inSql("av.biz_key",bizKeys));
//        sql.append(wheresql);
//        if (!StringUtils.isEmpty(queryParam)) {
//            // 是否生成
//            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
//                sql.append(" and av.is_pay_data_created = ? ");
//                sqlParams.add(queryParam.get("flag").toString());
//            }
//            // 是否变动
//            if (!StringUtils.isEmpty(queryParam.get("changeAgency"))) {
//                if ("0".equals(queryParam.get("changeAgency"))) {
//                    sql.append(" and av.is_salary_change = 0 ");
//                } else {
//                    sql.append(" and av.is_salary_change != 0 ");
//                }
//            }
//            // 工资类别
//            if (!StringUtils.isEmpty(queryParam.get("salaryType"))) {
//                sql.append(" and pe.merit_pay_code = ? ");
//                sqlParams.add(queryParam.get("salaryType").toString());
//            }
//        }
        sql.append(" group by  av.id");
        sql.append(") t");
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, sqlParams.toArray());
    }

    @Override
    public SalaryDataVo queryGenerateSalaryTotal(Map<String, Object> queryParam, List<String> childrenId,List<String>bizKeys) {
        List<Object> sqlParams = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select sum(realSalary) realSalary, sum(shouldSalary) shouldSalary,personCount from ( ");
        sql.append(" select ");
        sql.append(" @i:=@i+1 no, av.is_pay_data_created status, av.id, ai.agency_code,  ")
                .append(" ai.agency_name, av.salary_month month, sum(s1) realSalary, sum(s2) shouldSalary,count(*) personCount ");
        sql.append(" from ");
        sql.append(" (select   @i:=0) t, ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" sme ")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pe ")
                .append(" ON sme.agency_id = pe.agency_id and sme.person_id=pe.person_id ")
                .append(" join  ").append(TableName.AGENCY_VERIFY).append(" av ")
                .append( " ON av.agency_id = sme.agency_id  ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai ")
                .append(" ON av.agency_id = ai.id ");
        sql.append(" where av.is_month_end = ? and av.is_thirteen = ?")
                .append(" and pe.is_deleted = ? ")
                .append(" and pe.is_stop_salary = ?");

               // .append(" and av.salary_info_verified_code = ?");
        sqlParams.add(SystemDictConstant.IS_FALSE);
        sqlParams.add(SystemDictConstant.IS_FALSE);
        // and pi.is_deleted = ? sqlParams.add(SystemDictConstant.IS_FALSE);
        sqlParams.add(SystemDictConstant.IS_FALSE);
        sqlParams.add(SystemDictConstant.IS_FALSE);
        if (!StringUtils.isEmpty(queryParam.get("province"))){
            sql.append(" and sme.mof_div_code = ? ");
            sqlParams.add(queryParam.get("province"));
        }
        if (!StringUtils.isEmpty(queryParam.get("salaCardBank"))){
            sql.append(" and pe.sala_card_bank = ? ");
            sqlParams.add(queryParam.get("salaCardBank"));
        }
        //sqlParams.add("终审通过");
//        sql.append(" and ").append(SysUtils.inSql("sme.agency_id", childrenId));
        sql.append(" and ").append(SysUtils.inSql("av.biz_key",bizKeys));
        if (!StringUtils.isEmpty(queryParam)) {
            // 是否生成
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                sql.append(" and av.is_pay_data_created = ? ");
                sqlParams.add(queryParam.get("flag").toString());
            }
            // 是否变动
            if (!StringUtils.isEmpty(queryParam.get("changeAgency"))) {
                if ("0".equals(queryParam.get("changeAgency"))) {
                    sql.append(" and av.is_salary_change = 0 ");
                } else {
                    sql.append(" and av.is_salary_change != 0 ");
                }
            }
            // 工资类别
            if (!StringUtils.isEmpty(queryParam.get("salaryType"))) {
                sql.append(" and pe.merit_pay_code = ? ");
                sqlParams.add(queryParam.get("salaryType").toString());
            }
        }
        sql.append(" group by ai.agency_code, av.is_pay_data_created, av.id, ai.agency_name, av.salary_month ");
        sql.append(") t");
        return this.getjdbcTemplate().queryForObject(sql.toString(),new BeanPropertyRowMapper<>(SalaryDataVo.class), sqlParams.toArray());
    }

    @Override
    public List<Map<String, Object>> getPersonSalary(List<String> operateAgencyIds) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" ai.id \"id\", ai.agency_code \"agency_code\", ai.agency_name \"agency_name\", pi.state \"state\", pe.sala_card_bank \"sala_card_bank\", sum(s1) \"realSalary\", sum(s2) \"shouldSalary\" ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY).append(" av ")
                .append(" JOIN ").append(TableName.AGENCY_INFO).append(" ai ")
                .append(" ON av.agency_id = ai.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe ")
                .append(" ON ai.id = pe.agency_id")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ")
                .append(" ON pi.id = pe.person_id ")
                .append(" JOIN ").append(TableName.SALARY_MONTH_END).append(" sme ")
                .append(" ON pi.id = sme.person_id ");
        sql.append(" where ").append(" av.is_pay_data_created = ? ")
                .append(" AND pi.is_deleted = 0")
                .append(" AND pe.is_deleted = 0")
                .append(" AND av.is_month_end = ? ")
                .append(" AND av.is_thirteen = ? ")
                .append(" AND pe.is_stop_salary = ? ")
                .append(" and av.is_confirm_submit = ?");
        sql.append(" and ").append(SysUtils.inSql("ai.id", operateAgencyIds));
        sql.append(" GROUP BY ai.id, ai.agency_code, ai.agency_name, pi.state, pe.sala_card_bank ");
        sql.append(" ORDER BY agency_code,pi.state, pe.sala_card_bank ");
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add("1");
        return this.getjdbcTemplate().queryForList(sql.toString(), paramsList.toArray());
    }

    @Override
    public List<Map<String, Object>> getPersonOtherSalary(String agencyId, List<String> columnName) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" ai.id \"id\", ai.agency_code \"agency_code\", ai.agency_name \"agency_name\", pe.state \"state\", pe.merit_pay_code \"merit_pay_code\", pe.sala_card_bank \"sala_card_bank\" ");
        if (!StringUtils.isEmpty(columnName) && columnName.size() > 0) {
            for (String column : columnName) {
                sql.append(" ,sum(").append(column).append(") ").append(column);
            }
        }
        sql.append(" FROM ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" sme ")
                .append(" JOIN ").append(TableName.PERSON_EXT_SUBMIT).append(" pe ")
                .append(" ON sme.agency_id = pe.agency_id and sme.person_id=pe.person_id ")
                .append(" JOIN ").append(TableName.AGENCY_VERIFY).append(" av ")
                .append(" ON pe.agency_id = av.agency_id  ")
                .append(" JOIN ").append(TableName.AGENCY_INFO).append(" ai ")
                .append(" ON av.agency_id = ai.id ");
        sql.append(" where ").append(" av.is_pay_data_created = ? ")
                .append(" AND pe.is_deleted = 0")
                .append(" AND av.is_month_end = ? ")
                .append(" AND av.is_thirteen = ? ")
                .append(" AND pe.is_stop_salary = ? ")
                .append(" and av.is_confirm_submit = ?");
        sql.append(" and ai.id = ?");
        sql.append(" GROUP BY ai.id, ai.agency_code, ai.agency_name, pe.state, pe.sala_card_bank, pe.merit_pay_code ");
        sql.append(" ORDER BY agency_code,pe.state, pe.sala_card_bank ");
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add("1");
        paramsList.add(agencyId);
        return this.getjdbcTemplate().queryForList(sql.toString(), paramsList.toArray());
    }

    @Override
    public List<Map<String, Object>> getPayeeColumnName(List<String> operateAgencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT sas.salary_code \"column_name\", ssbi.salary_base_id \"salary_base_id\", ssbi.person_state \"person_state\", ssbi.agency_id \"agency_id\", ")
                .append(" spa.payee_acct_name, spa.payee_acct_no, spa.payee_acct_bank_name,ssbi.remark ");
        sql.append(" FROM ").append(TableName.SALARY_BASEDATA).append(" sas ")//原取值逻辑为从单位工资项挂接表取数
                .append(" JOIN ").append(TableName.SALARY_BASE_ITEMIZE).append(" ssbi ")
                .append(" ON  sas.id = ssbi.salary_base_id  ")
                .append(" JOIN ").append(TableName.PAYEE_ACCT).append(" spa ")
                .append(" on ssbi.payee_acct_id = spa.id and spa.is_deleted = 0");
        // sas.column_name != 's1' and
        sql.append(" WHERE sas.is_deleted = 0 and ssbi.is_deleted = 0 ")
                .append(" and ").append(SysUtils.inSql("ssbi.agency_id", operateAgencyIds));
        sql.append(" ORDER BY ssbi.agency_id ");
        return this.getjdbcTemplate().queryForList(sql.toString());
    }

    @Override
    public List<Map<String, Object>> getAgencyPayData(String isPayData, List<String> operateAgencyIds) {
        List<Object> sqlParams = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" av.id \"id\", ai.id \"agency_id\", ai.agency_code \"agency_code\", ")
                .append(" ai.agency_name \"agency_name\", av.salary_month \"month\", sum(s1) \"realSalary\", sum(s2) \"shouldSalary\"");        sql.append(" from ");
        sql.append(TableName.AGENCY_VERIFY).append(" av ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai ")
                .append( " on av.agency_id = ai.id ")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pe ")
                .append(" ON ai.id = pe.agency_id ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pi ")
                .append(" on pi.id = pe.person_id ")
                .append(" join  ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" sme ")
                .append(" ON pi.id = sme.person_id ");
        sql.append(" where av.is_pay_data_created = ? and av.is_month_end = ? and av.is_thirteen = ?")
                .append(" and pe.is_stop_salary = ?");
        sql.append(" and ").append(SysUtils.inSql("ai.id", operateAgencyIds));
        sqlParams.add(isPayData);
        sqlParams.add(SystemDictConstant.IS_FALSE);
        sqlParams.add(SystemDictConstant.IS_FALSE);
        sqlParams.add(SystemDictConstant.IS_FALSE);
        sql.append(" group by av.id, ai.id, ai.agency_code, ai.agency_name, av.salary_month ");
        sql.append(" order by agency_code ");
        return this.getjdbcTemplate().queryForList(sql.toString(), sqlParams.toArray());
    }

    @Override
    public void updatePayData(String agencyId, String payFlag) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.AGENCY_VERIFY);
        sql.append(" set is_pay_data_created = ?,payment_create_time=? where agency_id = ? and is_month_end = ? ");
        Object[] objects = new Object[] {
                payFlag,String.valueOf(DateUtils.getCurrentTimeStamp()), agencyId, SystemDictConstant.IS_FALSE
        };
        this.getjdbcTemplate().update(sql.toString(), objects);
    }

    @Override
    public void updatePayDatas(List<String> agencyIds, String payFlag,String paymentCreateTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.AGENCY_VERIFY);
        sql.append(" set is_pay_data_created = ?,payment_create_time=? where ").append(SysUtils.inSql("agency_id",agencyIds))
                .append(" and is_month_end = ? ");
        Object[] objects = new Object[] {
                payFlag,paymentCreateTime, SystemDictConstant.IS_FALSE
        };
        this.getjdbcTemplate().update(sql.toString(), objects);
    }

    @Override
    public void insertPayBackData(SalaryPaymentRequisitionBack salaryPaymentRequisitionBack) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.ALARY_PAYMENT_REQUISITION_BACK);
        sql.append("(id, biz_key, create_time, update_time, agency_id, agency_name, pay_id, pay_app_no, ")
                .append(" bgt_id, pay_app_amt, fund_type, payee_acct_name, payee_acct_no, payee_acct_bank_name, ")
                .append(" pay_type, pro_cat, gov_bgt_eco, dep_bgt_eco, pro, use_name, exp_func, is_deleted,mof_div_code )");
        sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        Object[] objects = new Object[]{
                salaryPaymentRequisitionBack.getId(), salaryPaymentRequisitionBack.getBizKey(), salaryPaymentRequisitionBack.getCreateTime(),
                salaryPaymentRequisitionBack.getUpdateTime(), salaryPaymentRequisitionBack.getAgencyId(),
                salaryPaymentRequisitionBack.getAgencyName(), salaryPaymentRequisitionBack.getPayId(), salaryPaymentRequisitionBack.getPayAppNo(),
                salaryPaymentRequisitionBack.getBgtId(),
                salaryPaymentRequisitionBack.getPayAppAmt(), salaryPaymentRequisitionBack.getFundType(), salaryPaymentRequisitionBack.getPayeeAcctName(),
                salaryPaymentRequisitionBack.getPayeeAcctNo(), salaryPaymentRequisitionBack.getPayeeAcctBankName(), salaryPaymentRequisitionBack.getPayType(),
                salaryPaymentRequisitionBack.getProCat(), salaryPaymentRequisitionBack.getGovBgtEco(), salaryPaymentRequisitionBack.getDepBgtEco(),
                salaryPaymentRequisitionBack.getPro(), salaryPaymentRequisitionBack.getUseName(), salaryPaymentRequisitionBack.getExpFunc(),
                salaryPaymentRequisitionBack.getIsDeleted(),salaryPaymentRequisitionBack.getMofDivCode()
        };
        this.getjdbcTemplate().update(sql.toString(), objects);
    }

    @Override
    public void batchInsertPayBackData(List<SalaryPaymentRequisitionBack> salaryPaymentRequisitionBacks) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.ALARY_PAYMENT_REQUISITION_BACK);
        sql.append("(id, biz_key, create_time, update_time, agency_id, agency_name, pay_id, pay_app_no, ")
                .append(" bgt_id, pay_app_amt, fund_type, payee_acct_name, payee_acct_no, payee_acct_bank_name, ")
                .append(" pay_type, pro_cat, gov_bgt_eco, dep_bgt_eco, pro, use_name, exp_func, is_deleted,mof_div_code )");
        sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramList = new ArrayList<>(salaryPaymentRequisitionBacks.size());
        for(SalaryPaymentRequisitionBack salaryPaymentRequisitionBack : salaryPaymentRequisitionBacks){
            Object[] objects = new Object[]{
                    salaryPaymentRequisitionBack.getId(), salaryPaymentRequisitionBack.getBizKey(), salaryPaymentRequisitionBack.getCreateTime(),
                    salaryPaymentRequisitionBack.getUpdateTime(), salaryPaymentRequisitionBack.getAgencyId(),
                    salaryPaymentRequisitionBack.getAgencyName(), salaryPaymentRequisitionBack.getPayId(), salaryPaymentRequisitionBack.getPayAppNo(),
                    salaryPaymentRequisitionBack.getBgtId(),
                    salaryPaymentRequisitionBack.getPayAppAmt(), salaryPaymentRequisitionBack.getFundType(), salaryPaymentRequisitionBack.getPayeeAcctName(),
                    salaryPaymentRequisitionBack.getPayeeAcctNo(), salaryPaymentRequisitionBack.getPayeeAcctBankName(), salaryPaymentRequisitionBack.getPayType(),
                    salaryPaymentRequisitionBack.getProCat(), salaryPaymentRequisitionBack.getGovBgtEco(), salaryPaymentRequisitionBack.getDepBgtEco(),
                    salaryPaymentRequisitionBack.getPro(), salaryPaymentRequisitionBack.getUseName(), salaryPaymentRequisitionBack.getExpFunc(),
                    salaryPaymentRequisitionBack.getIsDeleted(),salaryPaymentRequisitionBack.getMofDivCode()
            };
            paramList.add(objects);
        }

        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < 23; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void deletePayBackData(SalaryPaymentRequisitionBack salaryPaymentRequisitionBack) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.ALARY_PAYMENT_REQUISITION_BACK).append(" set ");
        sql.append(" is_deleted = 1 where id = ? ");
        Object[] objects = new Object[] {
                salaryPaymentRequisitionBack.getId()
        };
        this.getjdbcTemplate().update(sql.toString(), objects);
    }

    @Override
    public List<SalaryPaymentRequisitionBack> getPayBackData(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT sprb.* FROM ")
                .append(TableName.AGENCY_VERIFY).append(" av ")
                .append(" JOIN ").append(TableName.ALARY_PAYMENT_REQUISITION_BACK).append(" sprb ")
                .append(" ON av.agency_id = sprb.agency_id ");
        sql.append(" WHERE av.agency_id = ? and av.is_month_end = ? and sprb.is_deleted = ?");
        Object[] objects = new Object[]{
                queryParam.get("agencyId"), SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE
        };
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryPaymentRequisitionBack.class), objects);
    }

    @Override
    public List<Map<String, Object>> getRevokePayBackData(List<String> operateAgencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT sprb.id \"id\", sprb.pay_id \"pay_id\", av.agency_id \"agency_id\" ").append(" FROM ")
                .append(TableName.AGENCY_VERIFY).append(" av ")
                .append(" JOIN ").append(TableName.ALARY_PAYMENT_REQUISITION_BACK).append(" sprb ")
                .append(" ON av.agency_id = sprb.agency_id ");
        sql.append(" where av.is_month_end = 0  and sprb.is_deleted=0 ");
        sql.append(" and ").append(SysUtils.inSql("av.agency_id", operateAgencyIds));
       // Object[] objects = new Object[] {"终审通过"};
       //and av.salary_info_verified_code = ?
        return this.getjdbcTemplate().queryForList(sql.toString());
    }

    @Override
    public List<AgencyVerify> getBankDataStatus(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code,");
        sql.append(" salary_info_verified_code, person_info_verified_code, agency_info_verified_code,");
        sql.append(" is_submit_in_agency, is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit,");
        sql.append(" is_month_end, is_deleted, remark, is_salary_change, add_person_num, delete_person_num, is_thirteen");
        sql.append("    , audit_explain, is_pay_data_created, is_bank_data_created ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = ? and is_pay_data_created = ?");
        sql.append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
        Object[] objects = new Object[]{
                SystemDictConstant.IS_FALSE, SystemDictConstant.IS_TRUE
        };
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyVerify.class), objects);
    }

    @Override
    public List<Map<String, Object>> isPaymentEnough(String agencyId, List<String> columnName) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" ai.id \"id\", ai.agency_code \"agency_code\", ai.agency_name \"agency_name\",  pe.merit_pay_code \"merit_pay_code\", pe.sala_card_bank \"sala_card_bank\" ");
        if (!StringUtils.isEmpty(columnName) && columnName.size() > 0) {
            for (String column : columnName) {
                sql.append(" ,sum(").append(column).append(") ").append(column);
            }
        }
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY).append(" av ")
                .append(" JOIN ").append(TableName.AGENCY_INFO).append(" ai ")
                .append(" ON av.agency_id = ai.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe ")
                .append(" ON ai.id = pe.agency_id")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ")
                .append(" ON pi.id = pe.person_id ")
                .append(" JOIN ").append(TableName.SALARY_MONTH_END).append(" sme ")
                .append(" ON pi.id = sme.person_id ");
        sql.append(" where ")
                .append(" pi.is_deleted = 0")
                .append(" AND pe.is_deleted = 0")
                .append(" AND av.is_month_end = ? ");
        sql.append(" and ai.id = ?");
        sql.append(" GROUP BY ai.id, ai.agency_code, ai.agency_name, pi.state, pe.sala_card_bank, pe.merit_pay_code ");
        sql.append(" ORDER BY agency_code,pi.state, pe.sala_card_bank ");
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(agencyId);
        return this.getjdbcTemplate().queryForList(sql.toString(), paramsList.toArray());
    }

    @Override
    public List<Map<String, Object>> isMonthsPaymentEnough(String agencyId, List<String> columnName, String months) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" ai.id \"id\", ai.agency_code \"agency_code\", ai.agency_name \"agency_name\", pe.merit_pay_code \"merit_pay_code\", pe.sala_card_bank \"sala_card_bank\" ");
        if (!StringUtils.isEmpty(columnName) && columnName.size() > 0) {
            for (String column : columnName) {
                sql.append(" ,sum(").append(column).append(")*").append(months).append(" ").append(column);
            }
        }
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY).append(" av ")
                .append(" JOIN ").append(TableName.AGENCY_INFO).append(" ai ")
                .append(" ON av.agency_id = ai.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe ")
                .append(" ON ai.id = pe.agency_id")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ")
                .append(" ON pi.id = pe.person_id ")
                .append(" JOIN ").append(TableName.SALARY_MONTH_END).append(" sme ")
                .append(" ON pi.id = sme.person_id ");
        sql.append(" where ")
                .append(" pi.is_deleted = 0")
                .append(" AND pe.is_deleted = 0")
                .append(" AND av.is_month_end = ? ");
        sql.append(" and ai.id = ?");
        sql.append(" GROUP BY ai.id, ai.agency_code, ai.agency_name, pi.state, pe.sala_card_bank, pe.merit_pay_code ");
        sql.append(" ORDER BY agency_code,pi.state, pe.sala_card_bank ");
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(agencyId);
        return this.getjdbcTemplate().queryForList(sql.toString(), paramsList.toArray());
    }

    @Override
    public List<Map<String, Object>> isPaymentAllConnectPersonType(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" distinct d.id \"id\",d.agency_name \"agency_name\",c.name \"person_type\",e.salary_base_name \"salary_base_name\",p.show_name \"bank\" from ")
                .append("( select distinct agency_id,merit_pay_code,mof_div_code from   ")
                .append(TableName.PERSON_EXT)
                .append(" where ").append(SysUtils.inSql("agency_id", agencyIds))
                .append(" and is_deleted=0 ")
                .append(")   b ")
                .append(" join ")
                .append(TableName.BASE_DATA).append("  c ")
                .append("on b.merit_pay_code=c.code and b.mof_div_code=c.mof_div_code ")
                .append(" join ")
                .append(TableName.AGENCY_VERIFY).append("   d ")
                .append("on b.agency_id=d.agency_id and d.is_month_end=0 ")
                .append(" join ")
                .append(TableName.SALARY_BASE_ITEMIZE).append("  e ")
                .append("ON e.agency_id = b.agency_id and e.person_state=b.merit_pay_code")
                .append(" join ").append(TableName.PAYEE_ACCT).append(" p ")
                .append(" on e.payee_acct_id=p.id")
                .append(" join ")
                .append(TableName.SAL_PERSON_TYPE_SALARY).append("  f ")
                .append(" on e.salary_base_id=f.salary_id and b.merit_pay_code=f.person_type_code ")
                .append(" left join ")
                .append(TableName.AGENCY_SALARY_PAYMENT).append("  a ")
                .append(" on a.year=d.year and a.is_using=1 and a.agency_id=b.agency_id and a.person_type=b.merit_pay_code and a.salary_id=f.salary_id ")
                .append(" and a.is_deleted=").append(SystemDictConstant.NOT_DELETED)
                .append(" where a.id is null and e.is_deleted=").append(SystemDictConstant.NOT_DELETED);
        return this.getjdbcTemplate().queryForList(sql.toString());
    }
    @Override
    public List<Map<String, Object>> isSalaryAllConnectPersonType(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" distinct d.id \"id\",d.agency_name \"agency_name\",c.name \"person_type\" from ")//,sala_card_bank "bank"
                .append("( select distinct agency_id,merit_pay_code,mof_div_code from   ")
                .append(TableName.PERSON_EXT)
                .append(" where ").append(SysUtils.inSql("agency_id", agencyIds))
                .append(" and is_deleted=0 ")
                .append(")   b ")
                .append(" join ")
                .append(TableName.BASE_DATA).append("  c ")
                .append("on b.merit_pay_code=c.code and b.mof_div_code=c.mof_div_code ")
                .append(" join ")
                .append(TableName.AGENCY_INFO).append("   d ")
                .append("on b.agency_id=d.id ")
                .append(" left join ")
                .append(TableName.SALARY_BASE_ITEMIZE).append("  a ")
                .append(" on a.agency_id=b.agency_id and a.person_state=b.merit_pay_code and a.is_deleted=? ")
                .append(" where a.id is null ");
                Object[] objects=new Object[]{
                    SystemDictConstant.NOT_DELETED
                };

        return this.getjdbcTemplate().queryForList(sql.toString(),objects);
    }
    @Override
    public List<Map<String,Object>> getPersonType(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" distinct merit_pay_code,agency_id from ")
                .append(TableName.PERSON_EXT).append(" where ").append(SysUtils.inSql("agency_id", agencyIds));


        return this.getjdbcTemplate().queryForList(sql.toString());
    }
    /** 新支付推送代码20210426新增*/
    @Override
    public List<String> getPayeeColumnName(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT distinct sbd.salary_code ");

        sql.append(" FROM ").append(TableName.SALARY_BASE_ITEMIZE).append("  sbi ")
        .append(" join ").append(TableName.SALARY_BASEDATA).append("  sbd")
                .append(" on sbi.salary_base_id = sbd.id ")
        .append(" WHERE  sbi.is_deleted = 0 and sbd.is_deleted = 0 ")
//                .append(" and salary_base_code is not null ")
                .append(" and sbi.mof_div_code=? ");
        return super.getjdbcTemplate().queryForList(sql.toString(), String.class, province);
    }

    @Override
    public List<Map<String, Object>> getPersonOtherSalary(List<String> agencyIds, List<String> columnName,boolean is_agency) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" av.id \"id\", av.agency_code \"agency_code\", av.agency_name \"agency_name\", pe.state \"state\", pe.merit_pay_code \"merit_pay_code\", pe.sala_card_bank \"sala_card_bank\" ");
        if (!StringUtils.isEmpty(columnName) && columnName.size() > 0) {
            for (String column : columnName) {
                sql.append(" ,sum( nvl(").append(column).append(",0)) ").append(column);
            }
        }
        if(is_agency){//若是单位调用则查询单位表（暂只有冻结指标用）
            sql.append(" FROM ").append(TableName.SALARY_MONTH_END).append(" sme ")
                    .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe ");
        }else {
            sql.append(" FROM ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" sme ")
                    .append(" JOIN ").append(TableName.PERSON_EXT_SUBMIT).append(" pe ");
        }
        sql.append(" ON sme.agency_id = pe.agency_id and sme.person_id=pe.person_id ")
                .append(" JOIN ").append(TableName.AGENCY_VERIFY).append(" av ")
                .append(" ON pe.agency_id = av.agency_id  ");

        sql.append(" where ").append(" av.is_pay_data_created = ? ")
                .append(" AND pe.is_deleted = 0")
                .append(" AND av.is_month_end = ? ");
//                .append(" AND av.is_thirteen = ? ")
//                .append(" AND pe.is_stop_salary = ? ");
//                .append(" and av.is_confirm_submit = ?");
        sql.append(" and ").append(SysUtils.inSql("av.agency_id",agencyIds));
        sql.append(" GROUP BY av.id, av.agency_code, av.agency_name, pe.state, pe.sala_card_bank, pe.merit_pay_code ");
        sql.append(" ORDER BY av.agency_code,pe.state, pe.sala_card_bank ");
        paramsList.add(SystemDictConstant.IS_FALSE);
//        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(SystemDictConstant.IS_FALSE);
//        paramsList.add(SystemDictConstant.IS_FALSE);
//        if(is_agency){
//            paramsList.add(SystemDictConstant.IS_FALSE);
//        }else {
//            paramsList.add(SystemDictConstant.IS_TRUE);
//        }
        return this.getjdbcTemplate().queryForList(sql.toString(), paramsList.toArray());
    }
    @Override
    public void deleteBatchPayBack(List<SalaryPaymentTicket> salaryPaymentTickets,String mofDivCode) {
        List<String> paramsList = new ArrayList<>();
        for(SalaryPaymentTicket salaryPaymentTicket:salaryPaymentTickets){
            paramsList.add(salaryPaymentTicket.getPayId());
        }
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.ALARY_PAYMENT_REQUISITION_BACK).append(" set ");
        sql.append(" is_deleted = 1 where ")
            .append(" mof_div_code=? and ");
        sql.append(SysUtils.inSql("pay_id",paramsList));
        this.getjdbcTemplate().update(sql.toString(),mofDivCode);
    }
    @Override
    public List<Map<String, Object>> getPersonOtherSalarys(List<String> agencyIds, List<String> columnName,boolean is_agency, SalaryWarnMonth salaryWarnMonth) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        if(StringUtils.isEmpty(salaryWarnMonth.getWarnMonth())||salaryWarnMonth.getWarnMonth().equals("0")){
            salaryWarnMonth.setWarnMonth("1");
        }
        sql.append(" SELECT ");
        sql.append(" av.id as \"agency_id\", av.agency_code \"agency_code\", av.agency_name \"agency_name\", pe.merit_pay_code \"merit_pay_code\", pe.sala_card_bank \"sala_card_bank\" ");
        if (!StringUtils.isEmpty(columnName) && columnName.size() > 0) {
            for (String column : columnName) {
                sql.append(" ,sum( nvl(").append(column+"*"+salaryWarnMonth.getWarnMonth()).append(",0)) ").append(column);
            }
        }
            sql.append(" FROM ").append(TableName.SALARY_MONTH_END).append(" sme ")
                    .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe ");

        sql.append(" ON sme.agency_id = pe.agency_id and sme.person_id=pe.person_id ")
                .append(" JOIN ").append(TableName.AGENCY_INFO).append(" av ")
                .append(" ON pe.agency_id = av.id  ");

        sql.append(" where ")
                .append("  pe.is_deleted = 0")
                .append(" AND av.is_deleted = 0 ")
                .append(" AND pe.is_stop_salary = ? ");
        sql.append(" and ").append(SysUtils.inSql("av.id",agencyIds));
        sql.append(" GROUP BY av.id, av.agency_code, av.agency_name,  pe.sala_card_bank, pe.merit_pay_code ");
        sql.append(" ORDER BY agency_code, pe.sala_card_bank ");
        paramsList.add(SystemDictConstant.IS_FALSE);
        return this.getjdbcTemplate().queryForList(sql.toString(), paramsList.toArray());
    }

}
